#!/usr/bin/env python
# -*- coding: utf-8 -*-


from ConfigParser import ConfigParser
import json
import time

from engine import singleton
from dm_py_util import ERROR_LOG, DEBUG_LOG

from .client import SQLClient
from .connection import OperationalError
from .round_robin import RoundRobin


class SQLManager():

    def __init__(self):
        self.writer = None
        self.reader_rr = RoundRobin()
        self.max_idle_time = 7 * 3600
        self.connect_timeout = 0
        self.broken_clients = {}

    def init(self, config):
        self.config = config
        cfg = ConfigParser()
        cfg.read(config)

        self.max_idle_time = int(cfg.get('All', 'max_idle_time'))
        self.reuse_timeout = int(cfg.get('All', 'reuse_timeout'))
        self.connect_timeout = int(cfg.get('All', 'connect_timeout'))
        self.reader_num = int(cfg.get('All', 'reader_num'))

        conn = self._create_client(cfg, 'Writer')
        self.writer = conn
        if not conn:
            raise Exception('manager init writer error')

        for i in range(0, self.reader_num):
            conn = self._create_client(cfg, 'Reader_%d' % i)
            if cfg.has_option('Reader_%d' % i, 'weight'):
                weight = int(cfg.get('Reader_%d' % i, 'weight'))
            else:
                weight = 1
            self.reader_rr.add(conn, weight)
        if not len(self.reader_rr):
            raise Exception('no avaliable reader')
        DEBUG_LOG('SQLManager init success...')

    def _create_client(self, cfg, section):
        host = cfg.get(section, 'host')
        port = int(cfg.get(section, 'port'))
        db = cfg.get(section, 'db')
        user = cfg.get(section, 'user')
        password = cfg.get(section, 'password')
        client = SQLClient(host, port, db, user, password,
                           self.max_idle_time, self.connect_timeout)
        client.ping()
        return client

    def disconnect(self):
        for conn in self.reader_rr.pool:
            conn.disconnect()
        self.writer.disconnect()

    def get_reader_num(self):
        return len(self.reader_rr)

    def get_writer(self):
        return self.writer

    def get_reader(self):
        client = self.reader_rr.get()
        if not client:
            ERROR_LOG('all reader have down')
            client = self.get_writer()
        return client

    def execute(self, query, *parameters, **kwparameters):
        writer = self.get_writer()
        try:
            return writer.execute(query, *parameters, **kwparameters)
        except OperationalError as e:
            ERROR_LOG('query connection lost, writer have lost', e)
            raise
        except Exception as e:
            ERROR_LOG('write query failed', e)
            raise

    def insert(self, query, *parameters, **kwparameters):
        writer = self.get_writer()
        try:
            return writer.insert(query, *parameters, **kwparameters)
        except OperationalError as e:
            ERROR_LOG('query connection lost, writer have lost', e)
            raise
        except Exception as e:
            ERROR_LOG('write query failed', e)
            raise

    def update(self, query, *parameters, **kwparameters):
        writer = self.get_writer()
        try:
            return writer.update(query, *parameters, **kwparameters)
        except OperationalError as e:
            ERROR_LOG('query connection lost, writer have lost', e)
            raise
        except Exception as e:
            ERROR_LOG('write query failed', e)
            raise

    def executemany(self, query, parameters):
        writer = self.get_writer()
        try:
            return writer.executemany(query, parameters)
        except OperationalError as e:
            ERROR_LOG('query connection lost, writer have lost', e)
            raise
        except Exception as e:
            ERROR_LOG('write query failed', e)
            raise

    def executemany_rowcount(self, query, parameters):
        writer = self.get_writer()
        try:
            return writer.executemany_rowcount(query, parameters)
        except OperationalError as e:
            ERROR_LOG('query connection lost, writer have lost', e)
            raise
        except Exception as e:
            ERROR_LOG('write query failed', e)
            raise

    def query(self, query, *parameters, **kwparameters):
        self.try_reuse_broken()
        reader = self.get_reader()
        try:
            return reader.query(query, *parameters, **kwparameters)
        except OperationalError as e:
            ERROR_LOG('query connection lost, reader have lost', e)
            self.set_reader_broken(reader)
            raise
        except Exception as e:
            ERROR_LOG('query failed', e)
            raise

    def get(self, query, *parameters, **kwparameters):
        self.try_reuse_broken()
        reader = self.get_reader()
        try:
            return reader.get(query, *parameters, **kwparameters)
        except OperationalError as e:
            ERROR_LOG('query connection lost, reader have lost', e)
            self.set_reader_broken(reader)
            raise
        except Exception as e:
            ERROR_LOG('get query failed', e)
            raise

    def query_from_writer(self, query, *parameters, **kwparameters):
        writer = self.get_writer()
        try:
            return writer.query(query, *parameters, **kwparameters)
        except OperationalError as e:
            ERROR_LOG('writer query connection lost, writer have lost', e)
            raise
        except Exception as e:
            ERROR_LOG('write query failed', e)
            raise

    def transation(self):
        return self.writer.transation()

    def set_reader_broken(self, reader):
        if reader != self.writer:
            self.broken_clients[reader] = time.time()
            self.reader_rr.delete(reader)

    def try_reuse_broken(self):
        reuse_clients = []
        if len(self.reader_rr) != self.reader_num:
            for k,  v in self.broken_clients.items():
                if time.time() - v > self.reuse_timeout:
                    DEBUG_LOG('reuse client', k)
                    self.reader_rr.add(k)
                    reuse_clients.append(k)
        for c in reuse_clients:
            self.broken_clients.pop(c, None)


if __name__ == '__main__':
    m = SQLManager()
    m.init('./conf/sql.ini')
    # print m.get_reader_num()
    # print m.get_reader()
    # print m.get_reader()

    ret = m.query_from_writer('select * from update_user')
    print ret

    with m.transation() as trans:
        print trans
        trans.begin()
        try:
            trans.executemany('insert into users (uid , name) value (%s, %s)', ((
                12323, '哈哈'), (1232322, '哈哈')))
            # print trans.insert('insert into users (uid, name) value (%s, %s)', 123232, '哈哈')
            # trans.execute('insert into users (uid, name) value (%s, %s)', 12323, '哈哈')
            print trans.update('update users set uid = %s where uid = 12323', 12332323)
            # raise Exception('test....')
        except Exception as e:
            print 'error...', e
            trans.rollback()
        else:
            trans.commit()
    print m.update('update users set name = "哈哈哈222"')
    print m.executemany_rowcount('update users set name = %s', '哈哈哈2323哈哈哈')
    m.set_reader_broken(m.get_reader())
    print m.executemany('update users set name = %s', '哈哈哈2323哈哈哈')
    m.set_reader_broken(m.get_reader())
    ret = m.query('select * from update_user')
    print ret
    time.sleep(2)
    ret = m.query('select * from update_user')
    print ret
    m.insert('insert into users (uid, name) value (%s, %s)', 1232321, None)
